﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web.Mvc;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using ProjectBase.Core;
using ProjectBase.Core.Tools.Import;

namespace $safeprojectname$.Import.Excel
{
    public class ExcelImporter : IExcelImporter
    {
        public virtual IEnumerable<IValueContainer> Execute(IImporterData model)
        {
            Model = model;

            if (!LoadTemplate()
                || !ProcessDocument())
            {
                return null;
            }
            return Walk();
        }

        #region protected
        protected virtual bool LoadTemplate()
        {
            if (Model.DataSource.IsEmpty())
            {
                this.PublishError(Constants.Messages.NullValueWasProvided1
                    , "DataSource");
                return false;
            }

            try
            {
                Stream = new MemoryStream();
                Stream.Write(Model.DataSource, 0, Model.DataSource.Length);

                return true;
            }
            catch (Exception ex)
            {
                this.PublishFatal(ex);
            }
            return false;
        }
        protected virtual bool ProcessDocument()
        {
            try
            {
                using (var package = SpreadsheetDocument.Open(Stream, false))
                {
                    return Parse(package);
                }
            }
            catch (Exception ex)
            {
                this.PublishFatal(ex);
            }
            return false;
        }

        #endregion protected

        #region properties
        protected virtual IImporterData Model { get; set; }
        protected virtual Stream Stream { get; set; }
        protected virtual Row Headers { get; set; }
        protected virtual Row PropertyKeys { get; set; }
        protected virtual IEnumerable<Row> DataRows { get; set; }
        protected virtual SharedStringTablePart SharedStringTablePart { get; set; }
        protected virtual SharedStringTable SharedStringTable { get; set; }
        #endregion properties

        #region Parse & Walk

        protected virtual bool Parse(SpreadsheetDocument document)
        {
            Sheet sheet = document.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
            if (sheet.IsNull())
            {
                this.PublishError(Constants.Messages.ImporterWasNotProvidedWithFilledDataSource);
                // The specified worksheet does not exist.
                return false;
            }

            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
            Worksheet worksheet = worksheetPart.Worksheet;

            SharedStringTablePart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
            if (SharedStringTablePart.Is())
            {
                SharedStringTable = SharedStringTablePart.SharedStringTable;
            }


            var sourceRows = worksheet.Descendants<Row>();
            if (sourceRows.Count() < 2)
            {
                this.PublishError(Constants.Messages.ImporterExpectsHeadersInFirstRows);
                return false;
            }

            Headers = sourceRows.ElementAt(0);
            PropertyKeys = sourceRows.ElementAt(1);

            var dataRows = new List<Row>();
            // Iterate through the cells within the range and add their values to the sum.
            foreach (Row row in sourceRows.Skip(2))
            {
                dataRows.Add(row);
            }

            DataRows = dataRows;
            return true;
        }

        protected virtual IEnumerable<IValueContainer> Walk()
        {
            if (DataRows.IsEmpty())
            {
                this.PublishError(Constants.Messages.ImporterWasNotProvidedWithFilledDataSource);
                yield break;
            }
            foreach (var dataRow in DataRows)
            {
                bool isNotEmpty;
                var excelValueProvider = ExcelValueProvider.Create(PropertyKeys, dataRow, SharedStringTable, out isNotEmpty);
                if (isNotEmpty)
                {
                    yield return excelValueProvider;
                }
            }
        }

        #endregion Parse & Walk
    }
}
